const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser');
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'pdu',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});
const server = express();
server.use(bodyParser.urlencoded({
  extended: false
}));
const cors = require('cors');
server.use(cors({
  origin: '*'
}));

server.get('/PClassify', (req, res) => {
  // SQL语句以获取商品分类表的数据
  let sql = 'SELECT id,Pclassify FROM ProductsClassify ORDER BY id';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
  });
});

//获取制定分类下商品信息
server.get('/articles', (req, res) => {
  let cid = req.query.cid;
  let page = req.query.page? req.query.page : 1;
  let pagesize = 10;
  let offset = (page - 1) * pagesize;
  let rowcount;
  let sql = 'SELECT COUNT(id) AS count FROM products WHERE classifyId=?';
  pool.query(sql, [cid], (error, results) => {
    if (error) throw error;
    rowcount = results[0].count;
    let pagecount = Math.ceil(rowcount / pagesize);
    sql = 'SELECT id,price,image,DetailsFigure,title FROM products WHERE classifyId=? LIMIT ?,?';
    pool.query(sql, [cid, offset, pagesize], (error, results) => {
      if (error) throw error;
      res.send({ message: 'ok', code: 200, results: results, pagecount: pagecount });
    });
  });
});


server.get('/product',(req,res)=>{
  let cid=req.query.cid
  let sql='select id,price,image,DetailsFigure,title from products where id=?'
  pool.query(sql,[cid],(error,result)=>{
    if(error) throw error
    res.send({message:'ok',code:200,result:result})
  })
})

server.get('/all',(req,res)=>{
  let sql='select id,price,image,DetailsFigure,title from products'
  pool.query(sql,(error,result)=>{
    if(error) throw error
    res.send({message:'ok',code:200,result:result})
  })
})

//轮播图
server.get('/swipe',(req,res)=>{
let sql='select id,image from swipe'
  pool.query(sql,(error,result)=>{
    if(error) throw error
    res.send({message:'ok',code:200,result:result})
  })
})
server.listen(3000, () => {
  console.log('服务器成功!!!');
});
server.get('/like',(req,res)=>{
  let text='%'+req.query.text+'%'
  let sql="select id,price,image,DetailsFigure,title from products where title like ?"
  pool.query(sql,[text],(error,result)=>{
    if(error) throw error
    console.log(result);
    res.send({ message: 'ok', code: 200, result: result})
  })
})
//注册
server.post('/register',(req,res)=>{
  let name=req.body.uname
  let pwd=req.body.upwd
  let phone=req.body.phone
  let sql = 'SELECT COUNT(uid) AS count FROM user WHERE uname=?';
  pool.query(sql, [name], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT user(uname,phone,upwd) VALUES(?,?,?)';
      pool.query(sql, [name,phone,pwd], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  });
})
server.post('/login',(req,res)=>{
  let name=req.body.uname
  let pwd=req.body.upwd
  let sql='select uid,uname,upwd,phone from user where uname=? and upwd=?'
  pool.query(sql,[name,pwd],(error,result)=>{
    if(error) throw error
    console.log(result);
    if(result.length==0){
      res.send({code:201,msg:'login failed'})
    }else{
      res.send({msg:'ok',code:200,result:result[0]});
    }
  })
})